import json
import pandas as pd
import requests as rq

def main():
    LL = []
    df = pd.read_excel('1.xlsx', sheet_name='Sheet1', encoding='utf-8')
    print (list(df.columns))
    print(df)
    # 0:availability, 1:district, 2:hospitalName, 3:info, 4:_, 5:tel, 6:addr
    for row in range(0, 22):
        colL_p = []
        for col in range(0, 7):
            try:
                df.iloc[row, col] = df.iloc[row, col].replace('\n','；')
            except:
                pass

            try:
                df.iloc[row, col] = df.iloc[row, col].replace('\t','；')
            except:
                pass

            try:
                df.iloc[row, col] = df.iloc[row, col].replace(' ','_')
            except:
                pass

            try:
                df.iloc[row, col] = df.iloc[row, col].replace('；；','；')
            except:
                pass

            try:
                df.iloc[row, col] = df.iloc[row, col].replace('；；','；')
            except:
                pass

            try:
                if (df.iloc[row, col][-1] == '；'):
                    df.iloc[row, col] = df.iloc[row, col][:-1]
            except:
                pass

            if col == 6 and str(df.iloc[row, col]).find('；') != -1:
                try:
                    df.iloc[row, col] = df.iloc[row, col][:str(df.iloc[row, col]).find('；')]
                except:
                    pass
            colL_p.append(str(df.iloc[row, col]))

        colL = colL_p
        # for L in colL_p:
        #     colL.append([itemOne[0].replace('nan', '').replace('_', ' ') for itemOne in [th.split() for th in L]])
        # print(colL)
        if colL[2] != 'nan':
            # url = 'https://restapi.amap.com/v3/geocode/geo?key=de1fb1729e2e4f6fb20d2e6ea3ac8e27' \
            #       '&address=' + colL[6] + '&city=武汉市'
            notFound = False
            url = 'https://restapi.amap.com/v3/place/text?key=de1fb1729e2e4f6fb20d2e6ea3ac8e27' \
                  '&keywords=' + colL[6] + '&types=医疗保健服务&city=武汉市&children=0&offset=1&page=1&extensions=all'
            response = rq.get(url)
            result = response.json()
            print(url, result)

            if len(result['pois']) == 0:
                notFound = True
            else:
                for k, v in result['pois'][0].items():
                    if v == []:
                        result['pois'][0][k] = ''
            # except:
            #     url = 'https://restapi.amap.com/v3/place/text?key=de1fb1729e2e4f6fb20d2e6ea3ac8e27' \
            #           '&keywords=' + colL[2] + '&types=医疗保健服务&city=武汉市&children=0&offset=1&page=1&extensions=all'
            #     response = rq.get(url)
            #     result = response.json()
            #     print(url, result)
            #     if len(result['pois']) == 0:
            #         notFound = True
            #     else:
            #         for k, v in result['pois'][0].items():
            #             if v == []:
            #                 result['pois'][0][k] = ''

            if notFound:
                fmt_addr = colL[6]
                fmt_name = colL[2]
                fmt_type1 = ''
                fmt_type2 = ''
                fmt_alias = ''
                fmt_district = colL[1]
                fmt_lng = '-999'
                fmt_lat = '-999'
                fmt_tel = ''
            else:
                fmt_addr = result['pois'][0]['address']
                fmt_name = result['pois'][0]['name']
                fmt_type1 = result['pois'][0]['type'].split(';')[1]
                fmt_type2 = result['pois'][0]['type'].split(';')[2]
                fmt_alias = result['pois'][0]['alias'].replace('|','；')
                fmt_district = result['pois'][0]['adname']
                fmt_lng = result['pois'][0]['location'].split(',')[0]
                fmt_lat = result['pois'][0]['location'].split(',')[1]
                fmt_tel = result['pois'][0]['tel'].replace(';','；')
            # fmt_entrance_lng = result['pois'][0]['entr_location'].split(',')[0]
            # fmt_entrance_lat = result['pois'][0]['entr_location'].split(',')[1]

            LL.append({'hospitalName': fmt_name,
                       'hospitalAlias': fmt_alias,
                       'availability': colL[0],
                       'info': colL[3],
                       'tel': colL[5],
                       'tel_GaodeMapAPI': fmt_tel,
                       'district': fmt_district,
                       'address': fmt_addr,
                       'type1': fmt_type1,
                       'type2': fmt_type2,
                       'lng': float(fmt_lng),
                       'lat': float(fmt_lat),
                       })
            # LL.append([colL[2], colL[0], fmt_district, fmt_addr, fmt_lng, fmt_lat, colL[5], colL[3]])
        # hospitalName, availability, address, tel, info
    writeStr = ''
    outJson = {'dnaTest':[]}
    for L in LL:
        outJson['dnaTest'].append(L)
        # outJson['dnaTest'].append([itemOne[0].replace('nan','').replace('_',' ') for itemOne in [th.split() for th in L]])

    with open('1out.json','w') as fw:
        fw.write(json.dumps(outJson, ensure_ascii=False, separators=(',',':')))
if __name__ == '__main__':
    main()